<?php
/**
 * Created by PhpStorm.
 * User: zhongyb
 * Date: 16-9-13
 * Time: 上午10:57
 */
namespace common\components\yunke;

use common\error\ErrorInfo;
use common\repositories\common\MyscrmDatabaseInstanceConfigRepository;
use common\repositories\config\ConfigSettingRepository;
use Yii;
use yii\base\Component;
use yii\base\Exception;

class DatabaseInstance extends Component
{
    public $dbConnection = [];

    public function __construct($config = [])
    {
        parent::__construct($config);
    }

    /**
     * 创建连接
     * @param $instanceId
     * @return object|\yii\db\Connection
     * @throws Exception
     */
    public function buildConnection($instanceId)
    {
        if(isset($this->dbConnection[$instanceId])){
            return $this->dbConnection[$instanceId];
        }

        /** @var MyscrmDatabaseInstanceConfigRepository $databaseInstanceConfigRepository */
        $databaseInstanceConfigRepository = Yii::createObject(MyscrmDatabaseInstanceConfigRepository::className());
        $instanceInfo = $databaseInstanceConfigRepository->getConfigByInstanceId($instanceId);

        if(empty($instanceInfo)) {
            throw new Exception('找不到可用实例：' . $instanceId);
        }

        //创建连接
        /** @var \yii\db\Connection $connection */
        $connection = Yii::createObject(\yii\db\Connection::className());

        $connection->dsn = 'mysql:host=' . $instanceInfo['host'] . $this->makePortString($instanceInfo);
        $connection->username = $instanceInfo['admin_name'];
        $connection->password = $instanceInfo['admin_password'];
        $connection->charset = 'utf8';
        $connection->tablePrefix = '';

        $this->dbConnection[$instanceId] = $connection;

        return $connection;
    }

    private function makePortString($instanceInfo)
    {
        if($instanceInfo['port']){
            return ';port=' . $instanceInfo['port'];
        }

        return ';port=3306';
    }

    public function execute($instanceId, $sql)
    {
        try {
            $connection = $this->buildConnection($instanceId);

            $connection->createCommand($sql)->execute();

            return true;
        }catch (\Exception $ex){
            Yii::error((string)$ex);
            return ErrorInfo::setAndReturn(-1, $ex->getMessage(),$ex->getMessage());
        }
    }

    /**
     * 创建数据库
     * @param $instanceId
     * @param $databaseName
     * @return bool
     */
    public function createDatabase($instanceId, $databaseName)
    {
        $sql = sprintf("CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;", $databaseName);

        return $this->execute($instanceId, $sql);
    }

    /**
     * 删除数据库
     * @param $instanceId
     * @param $databaseName
     * @return bool
     */
    public function deleteDatabase($instanceId, $databaseName)
    {
        $sql = sprintf("drop database if exists %s;", $databaseName);

        return $this->execute($instanceId, $sql);
    }

    /**
     * 给数据库赋权
     * @param $instanceId
     * @param $databaseName
     * @return bool
     * @throws Exception
     */
    public function grantAccountPrivilege($instanceId, $databaseName)
    {
        /** @var MyscrmDatabaseInstanceConfigRepository $databaseInstanceConfigRepository */
        $databaseInstanceConfigRepository = Yii::createObject(MyscrmDatabaseInstanceConfigRepository::className());
        $instanceInfo = $databaseInstanceConfigRepository->getConfigByInstanceId($instanceId);

        if (empty($instanceInfo)) {
            throw new Exception('找不到可用实例：' . $instanceId);
        }

        $sql = "GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON " . $databaseName . ".* to " . $instanceInfo['username'] . "@'%';";

        return $this->execute($instanceId, $sql);
    }

    /**
     * 检查数据库是否已存在
     * @param $instanceId
     * @param $databaseName
     * @return bool
     */
    public function checkDatabaseExist($instanceId, $databaseName)
    {
        $sql = "SELECT * FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`=:databaseName;";

        try {
            $connection = $this->buildConnection($instanceId);

            $existResult = $connection->createCommand($sql, [':databaseName'=> $databaseName])->execute();

            if($existResult) {
                return true;
            }

            return false;

        }catch (\Exception $ex){
            Yii::error((string)$ex);
            return ErrorInfo::setAndReturn(-1, $ex->getMessage(),$ex->getMessage());
        }
    }

    /**
     * 根据实例ID获取实例详情
     * @param $instanceId
     * @return array
     */
    public function getInstanceByInstanceId($instanceId)
    {
        /** @var MyscrmDatabaseInstanceConfigRepository $databaseInstanceConfigRepository */
        $databaseInstanceConfigRepository = Yii::createObject(MyscrmDatabaseInstanceConfigRepository::className());
        return $databaseInstanceConfigRepository->getConfigByInstanceId($instanceId);
    }

    /**
     * 根据实例ID获取实例列表
     * @param $instanceIds
     * @return array
     */
    public function getInstanceListByInstanceIds($instanceIds)
    {
        /** @var MyscrmDatabaseInstanceConfigRepository $databaseInstanceConfigRepository */
        $databaseInstanceConfigRepository = Yii::createObject(MyscrmDatabaseInstanceConfigRepository::className());
        return $databaseInstanceConfigRepository->getConfigListByInstanceIds($instanceIds);
    }

}